const mysql = require('mysql2/promise');

const dbConfig = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs'
};

async function migrateRoomIdToSenderId() {
    let connection;

    try {
        console.log('🔌 连接到数据库...');
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ 数据库连接成功');

        // 1. 检查表是否存在
        console.log('🔍 检查表结构...');
        const [tables] = await connection.execute("SHOW TABLES LIKE 'system_messages'");
        if (tables.length === 0) {
            console.log('❌ 系统消息表不存在，请先创建表');
            return;
        }

        // 2. 检查字段是否存在
        const [columns] = await connection.execute("DESCRIBE system_messages");
        const hasRoomId = columns.some(col => col.Field === 'roomId');
        const hasSenderId = columns.some(col => col.Field === 'senderId');

        if (!hasRoomId && !hasSenderId) {
            console.log('❌ 既没有 roomId 字段也没有 senderId 字段');
            return;
        }

        if (hasSenderId) {
            console.log('✅ senderId 字段已存在，无需迁移');
            return;
        }

        if (hasRoomId) {
            console.log('🔄 开始迁移：将 roomId 字段重命名为 senderId...');

            // 3. 重命名字段
            await connection.execute(`
                ALTER TABLE \`system_messages\` 
                CHANGE COLUMN \`roomId\` \`senderId\` int NOT NULL DEFAULT '0' 
                COMMENT '发送者ID，对应WebSocket消息的data.rid字段'
            `);
            console.log('✅ 字段重命名成功');

            // 4. 更新示例数据
            console.log('🔄 更新示例数据...');
            const [updateResult] = await connection.execute(`
                UPDATE \`system_messages\` 
                SET \`senderId\` = 0 
                WHERE \`senderId\` = 1000 AND \`senderName\` = '系统'
            `);
            console.log(`✅ 更新了 ${updateResult.affectedRows} 条记录`);

            // 5. 验证迁移结果
            console.log('🔍 验证迁移结果...');
            const [newColumns] = await connection.execute('DESCRIBE system_messages');
            const senderIdColumn = newColumns.find(col => col.Field === 'senderId');

            if (senderIdColumn) {
                console.log('✅ 迁移成功！新字段信息:');
                console.log(`  字段名: ${senderIdColumn.Field}`);
                console.log(`  类型: ${senderIdColumn.Type}`);
                console.log(`  默认值: ${senderIdColumn.Default}`);
                console.log(`  注释: ${senderIdColumn.Comment}`);
            }

            // 6. 显示示例数据
            console.log('🔍 显示示例数据...');
            const [dataRows] = await connection.execute('SELECT id, messageType, senderName, senderId, content FROM system_messages LIMIT 3');
            console.log('📊 数据示例:');
            dataRows.forEach((row, index) => {
                console.log(`  记录${index + 1}: ID=${row.id}, 类型=${row.messageType}, 发送者=${row.senderName}, 发送者ID=${row.senderId}`);
            });

        }

        console.log('\n🎉 字段迁移完成！');
        console.log('📝 注意事项:');
        console.log('   - roomId 字段已重命名为 senderId');
        console.log('   - 默认值从 1000 改为 0');
        console.log('   - 字段含义更准确地反映为发送者ID');
        console.log('   - 请确保应用程序代码已更新以使用新字段名');

    } catch (error) {
        console.error('❌ 迁移失败:', error.message);
        throw error;
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }
}

// 运行迁移脚本
if (require.main === module) {
    migrateRoomIdToSenderId()
        .then(() => {
            console.log('✅ 迁移脚本执行完成');
            process.exit(0);
        })
        .catch((error) => {
            console.error('❌ 迁移脚本执行失败:', error);
            process.exit(1);
        });
}

module.exports = { migrateRoomIdToSenderId };
